import requests
import string
import psycopg2 as pg
from pymongo import MongoClient
import logging
import datetime

logger = logging.getLogger(__name__)
hdlr = logging.FileHandler(__name__ + '.log')
formatter = logging.Formatter('%(asctime)s %(levelname)s %(message)s')
hdlr.setFormatter(formatter)
logger.addHandler(hdlr)
logger.setLevel(logging.DEBUG)

uri = 'https://od-api.oxforddictionaries.com/api/v1/search/en?q={}&prefix=true&offset={}'
headers = {'app_id': 'bf7d4111', 'app_key': '2df0557cb992e82b26df5fb865f93472'}
msg = '============ Processing words start with {}, {} words processed, to be processed: {} ============'
letters = string.ascii_lowercase
provider = ''
total = 1

db_info = {
    'host': '120.79.172.230',
    'port': '5432',
    'user': 'fqzhang',
    'password': 'Duke/63805',
    'dbname': 'datageek'
}

connection = pg.connect(**db_info)


def insert(word_list):
    cur = connection.cursor()
    sql = r'insert into datageek.wordlist(word,wordid,matchstring,matchtype,region,provider,total) values({})'
    values = r'%(word)s,%(wordid)s,%(matchString)s,%(matchType)s,%(region)s,%(provider)s,%(total)s'
    sql = sql.format(values)
    count = 0
    for word in word_list:
        logger.debug(word)
        cur.execute(sql, word)
        count += 1
        if count == 1000:
            count = 0
            connection.commit()
    connection.commit()


def fetch_word_list(start_letter, offset=0):
    url = uri.format(start_letter, offset)
    res = requests.get(url=url, headers=headers)
    if res.status_code == 200:
        data = res.json()
        return data['results'], data['metadata']
    else:
        raise Exception('Error code: ' + str(res.status_code))


def process_entry(entry):
    entry['wordid'] = entry['id']
    del entry['id']
    if 'matchForm' in entry:
        del entry['matchForm']
    entry['provider'] = provider
    entry['total'] = total
    return entry


def load_word_list():
    for letter in letters:
        total = 1
        offset = 0
        while offset < total:
            results, metadata = fetch_word_list(start_letter=letter, offset=offset)
            offset += len(results)
            total = metadata['total']
            provider = metadata['provider']
            word_list = tuple(map(process_entry, results))
            insert(word_list=word_list)
            print(msg.format(letter, offset, total - offset))
            logger.debug(msg.format(letter, offset, total - offset))


def load_word():
    uri = 'https://od-api.oxforddictionaries.com/api/v1/entries/en/{}'
    with pg.connect(**db_info) as conn:
        cursor = conn.cursor()
        sql = r'select distinct word from datageek.wordlist order by word limit 5'
        cursor.execute(sql)
        item = cursor.fetchone()
        while item is not None:
            w = item[0]
            res = requests.get(url=uri.format(w), headers=headers)
            if res.status_code == 200:
                insert = r'insert into datageek.OXFORD_DICT(wordid,entry,metadata) values(%(wordid)s,%(entry)s,%(metadata)s)'
                data = {'wordid': w, 'entry': res.json()['results'][0], 'metadata': res.json()['metadata']['provider']}
                with conn.cursor() as curr_insert:
                    curr_insert.execute(insert, data)
                conn.commit()
            item = cursor.fetchone()


def mongo_test():
    client = MongoClient('mongodb://120.79.172.230:27017/',
                         username='fqzhang',
                         password='Duke/63805',
                         authSource='testdb',
                         authMechanism='SCRAM-SHA-1')
    db = client.test_db
    collection = db.test
    post = {
        "author": "Mike",
        "text": "My first blog post!",
        "tags": ["mongodb", "python", "pymongo"],
        "date": datetime.datetime.utcnow()
    }
    pid = collection.insert_one(post).inserted_id
    print(pid)


mongo_test()
